package cn.neu.edu.Dao;

import java.sql.SQLException;
import java.util.ArrayList;

import cn.neu.edu.Entity.Department;
import cn.neu.edu.Entity.Patient;
import cn.neu.edu.Entity.Register;
import cn.neu.edu.Entity.User;
public class RegisterDao {
	private static RegisterDao instance = new RegisterDao();
	
	private RegisterDao(){
	}
	
	public static RegisterDao getInstance() {
		return instance;
	}
	public double getRegisterCost(String name)  {
		String sql = "select price from registlevel where name=?";
		Object[] list = new Object[1];
		list[0] = name;
		DBUtil.excuteQuery(sql, list);
		try {
			if(DBUtil.getResultSet().next())
				return DBUtil.getResultSet().getDouble(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public ArrayList<String> getDoctorName(String depname, String levelname) throws SQLException, ClassNotFoundException {
		String sql = "select user.name from user join department on depid=department.id join registlevel on levelid=registlevel.id where department.name=? and registlevel.name=?";
		Object[] list = new Object[2];
		list[0] = depname;
		list[1] = levelname;
		DBUtil.excuteQuery(sql, list);
		ArrayList<String> names = new ArrayList<>();
		while(DBUtil.getResultSet().next())
			names.add(DBUtil.getResultSet().getString(1));
		DBUtil.closeAll();
		return names;
	}
	public Patient getPatientBymedicalNumber(int medicalNumber) {
		String sql = "SELECT  name, sex, birthdate, age, agetype, idNum, address FROM medicalhistory JOIN register ON id = registid  JOIN patient ON patientidnum = idNum WHERE medicalNum = ?";
		Object[] list = new Object[1];
		list[0] = medicalNumber;
		DBUtil.setPreparedStatement(sql, list);
		Patient p = new Patient();
		try {
			if(DBUtil.getResultSet().next()) {
				p.setName(DBUtil.getResultSet().getString(1));
				p.setSex(DBUtil.getResultSet().getInt(2));
				p.setBirthday(DBUtil.getResultSet().getString(3));
				p.setAge(DBUtil.getResultSet().getInt(4));
				p.setAgetype(DBUtil.getResultSet().getString(5));
				p.setIdNum(DBUtil.getResultSet().getString(6));
				p.setAddress(DBUtil.getResultSet().getString(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.closeAll();
		return p;
	}
	
	public Patient getPatientByIdNum(String idNum) {
		String sql = "SELECT  * FROM patient WHERE idNum = ?";
		Object[] list = new Object[1];
		list[0] = idNum;
		DBUtil.excuteQuery(sql, list);
		Patient p = new Patient();
		try {
			if(DBUtil.getResultSet().next()) {
				p.setName(DBUtil.getResultSet().getString(1));
				p.setSex(DBUtil.getResultSet().getInt(2));
				p.setBirthday(DBUtil.getResultSet().getString(3));
				p.setAge(DBUtil.getResultSet().getInt(4));
				p.setAgetype(DBUtil.getResultSet().getString(5));
				p.setIdNum(DBUtil.getResultSet().getString(6));
				p.setAddress(DBUtil.getResultSet().getString(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.closeAll();
		return p;
	}
	
	public ArrayList<Register> getRegisterByMedicalNum(int medicalNumber, int number, int counts){
		ArrayList<Register> registers = new ArrayList<>();
		String sql = "select * from register where id in(select registid from medicalhistory where medicalnum = ?) limit ?,?";
		Object[] list = new Object[3];
		list[0] = medicalNumber;
		list[1] = number;
		list[2] = counts;
		DBUtil.excuteQuery(sql, list);
		try {
			while(DBUtil.getResultSet().next()) {
				registers.add(new Register(DBUtil.getResultSet().getInt(1), DBUtil.getResultSet().getString(2),
						DBUtil.getResultSet().getInt(3), DBUtil.getResultSet().getString(4), DBUtil.getResultSet().getString(5), 
						DBUtil.getResultSet().getInt(6), DBUtil.getResultSet().getInt(7),DBUtil.getResultSet().getInt(8), 
						DBUtil.getResultSet().getInt(9), DBUtil.getResultSet().getString(10)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return registers;
	}
	
	
	
	public int insertRegiser(String patientidnum, String healdate,int docid, int regid , int whetherMedical , int settlement, String noon) {
		String sql = "INSERT INTO `register` ( `patientidnum`, `healdate`, `docid`, `regid`, `whetherMedical`, `settlement`, `noon`,`registdate`) VALUES (?, ?, ?, ?, ?, ?, ?, now())";
		Object[] list = new Object[7];
		list[0] = patientidnum;
		list[1] = healdate;
		list[2] = docid;
		list[3] = regid;
		list[4] = whetherMedical;
		list[5] = settlement;
		list[6] = noon;
		return DBUtil.excuteUpdate(sql, list);
	}
	public int dropRegist(int id) {
		String sql = "update register set healstate=3 where id = ?";
		Object[] list = new Object[1];
		list[0] = id;
		return DBUtil.excuteUpdate(sql, list);
	}
	public int getRegisterState(int id) {
		String sql = "select healstate from register where id = ?";
		Object[] list = new Object[1];
		list[0] = id;
		DBUtil.excuteQuery(sql, list);
		try {
			if(DBUtil.getResultSet().next())
				return DBUtil.getResultSet().getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	public int insertPatient(String name,int sex,String birthdate,int age, String ageType, String idnum,String address) {
		String sql = "insert into patient values(?,?,?,?,?,?,?)";
		Object[] list = new Object[7];
		list[0] = name;
		list[1] = sex;
		list[2] = birthdate;
		list[3] = age;
		list[4] = ageType;
		list[5] = idnum;
		list[6] = address;
		return DBUtil.excuteUpdate(sql, list);
	}
	public int insertMedicalHistory(int medicalNumber, int registId) {
		String sql = "insert into MedicalHistory values(?,?)";
		Object[] list = new Object[2];
		list[0] = medicalNumber;
		list[1] = registId;
		return DBUtil.excuteUpdate(sql, list);
	}
	public int regist(String name ,int sex , String birthday ,int age ,String agetype ,String idNnum,
			String address ,int docid ,int regid ,int whetherMedical ,int settlement ,String noon) {
		String sql = "{call regist(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
		Object[] list = new Object[12];
		list[0] = name;
		list[1] = sex;
		list[2] = birthday;
		list[3] = age;
		list[4] = agetype;
		list[5] = idNnum;
		list[6] = address;
		list[7] = docid;
		list[8] = regid;
		list[9] = whetherMedical;
		list[10] = settlement;
		list[11] = noon;
		return DBUtil.setCallableStatementAndExcute(sql, list);
	}
	
	public ArrayList<Department> getAllDepartment() {
		ArrayList<Department> departments = new ArrayList<>();
		String sql = "select id,name from department";
		DBUtil.excuteQuery(sql, null);
		try {
			while(DBUtil.getResultSet().next()) {
				departments.add(new Department(DBUtil.getResultSet().getInt(1), DBUtil.getResultSet().getString(2)));
			}
			DBUtil.closeAll();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return departments;
	}
	
	public ArrayList<User> getDoctorByDepidLevelid(int depid, int levelId){
		ArrayList<User> users = new ArrayList<>();
		String sql = "select id,name from user where depid=? and levelid=?";
		Object[] list = new Object[2];
		list[0] = depid;
		list[1] = levelId;
		DBUtil.excuteQuery(sql, list);
		try {
			while(DBUtil.getResultSet().next()) {
				users.add(new User(DBUtil.getResultSet().getInt(1), DBUtil.getResultSet().getString(2)));
			}
			DBUtil.closeAll();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}
	
	public double getRegistCostById(int id) {
		double cost = -1;
		String sql = "select price from registlevel where id = ?";
		Object[] list = new Object[1];
		list[0] = id;
		DBUtil.excuteQuery(sql, list);
		try {
			if(DBUtil.getResultSet().next()) {
				cost= DBUtil.getResultSet().getDouble(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cost;
	}
	
}
